#!/bin/bash
# dws_1d_to_dws_nd.sh all/表名 [日期]
# 1、判断参数是否传入
if [ $# -lt 1 ]; then
    echo "必须输入all/表名"
    exit 1
fi

table_name=$1
# 2、判断日期是否传入
[ "$2" ] && date=$2 || date=$(date -d '-1 day' +%F)

# 验证日期格式是否为 YYYY-MM-DD
if ! date -d "$date" +%Y-%m-%d >/dev/null 2>&1; then
    echo "日期格式错误，请使用 YYYY-MM-DD 格式"
    exit 1
fi

# 定义函数来执行 SQL
execute_sql() {
    local sql=$1
    /opt/module/hive/bin/hive -e "use medical;set hive.exec.dynamic.partition.mode=nonstrict;$sql"
    if [ $? -ne 0 ]; then
        echo "执行 SQL 失败: $sql"
        exit 1
    fi
}

# 根据表名匹配每日加载
case $table_name in
    "all")
        # 10.2.1 交易域医院患者性别年龄段粒度问诊最近n日汇总表
        sql="insert overwrite table dws_trade_hospital_gender_age_group_consultation_nd
    partition (dt = '$date')
select hospital_id,
       hospital_name,
       gender_code,
       gender,
       age_group,
       sum(if(dt >= date_add('$date', -6), consultation_amount, 0)) consultation_amount_7d,
       sum(if(dt >= date_add('$date', -6), consultation_count, 0))  consultation_count_7d,
       sum(consultation_amount)                                          consultation_amount_30d,
       sum(consultation_count)                                           consultation_count_30d
from dws_trade_hospital_gender_age_group_consultation_1d
where dt >= date_add('$date', -29)
group by hospital_id,
         hospital_name,
         gender_code,
         gender,
         age_group;"
        execute_sql "$sql"

        # 10.2.2 交易域医院患者性别年龄段粒度问诊支付成功最近n日汇总表
        sql="insert overwrite table dws_trade_hospital_gender_age_group_consultation_pay_suc_nd
    partition (dt = '$date')
select hospital_id,
       hospital_name,
       gender_code,
       gender,
       age_group,
       sum(if(dt >= date_add('$date', -6), consultation_pay_suc_amount, 0)) consultation_pay_suc_amount_7d,
       sum(if(dt >= date_add('$date', -6), consultation_pay_suc_count, 0))  consultation_pay_suc_count_7d,
       sum(consultation_pay_suc_amount)                                          consultation_pay_suc_amount_30d,
       sum(consultation_pay_suc_count)                                           consultation_pay_suc_count_30d
from dws_trade_hospital_gender_age_group_consultation_pay_suc_1d
where dt >= date_add('$date', -29)
group by hospital_id,
         hospital_name,
         gender_code,
         gender,
         age_group;"
        execute_sql "$sql"

        # 10.2.3 交易域医院患者性别年龄段粒度处方开单最近n日汇总表
        sql="insert overwrite table dws_trade_hospital_gender_age_group_prescription_nd
    partition (dt = '$date')
select hospital_id,
       hospital_name,
       gender_code,
       gender,
       age_group,
       sum(if(dt >= date_add('$date', -6), prescription_amount, 0)) prescription_amount_7d,
       sum(if(dt >= date_add('$date', -6), prescription_count, 0))  prescription_count_7d,
       sum(prescription_amount)                                          prescription_amount_30d,
       sum(prescription_count)                                           prescription_count_30d
from dws_trade_hospital_gender_age_group_prescription_1d
where dt >= date_add('$date', -29)
group by hospital_id,
         hospital_name,
         gender_code,
         gender,
         age_group;"
        execute_sql "$sql"

        # 10.2.4 交易域医院患者性别年龄段粒度处方开单支付成功最近n日汇总表
        sql="insert overwrite table dws_trade_hospital_gender_age_group_prescription_pay_suc_nd
    partition (dt = '$date')
select hospital_id,
       hospital_name,
       gender_code,
       gender,
       age_group,
       sum(if(dt >= date_add('$date', -6), prescription_pay_suc_amount, 0)) prescription_pay_suc_amount_7d,
       sum(if(dt >= date_add('$date', -6), prescription_pay_suc_count, 0))  prescription_pay_suc_count_7d,
       sum(prescription_pay_suc_amount)                                          prescription_pay_suc_amount_30d,
       sum(prescription_pay_suc_count)                                           prescription_pay_suc_count_30d
from dws_trade_hospital_gender_age_group_prescription_pay_suc_1d
where dt >= date_add('$date', -29)
group by hospital_id,
         hospital_name,
         gender_code,
         gender,
         age_group;"
        execute_sql "$sql"

        # 10.2.5 交易域医生粒度问诊最近n日汇总表
        sql="insert overwrite table dws_trade_doctor_consultation_nd
    partition (dt = '$date')
select doctor_id,
       doctor_name,
       sum(if(dt >= date_add('$date', -6), consultation_count, 0)) consultation_count_7d,
       sum(consultation_count)                                          consultation_count_30d
from dws_trade_doctor_consultation_1d
where dt >= date_add('$date', -29)
group by doctor_id,
         doctor_name;"
        execute_sql "$sql"
        ;;
    "dws_trade_hospital_gender_age_group_consultation_nd")
        sql="insert overwrite table dws_trade_hospital_gender_age_group_consultation_nd
    partition (dt = '$date')
select hospital_id,
       hospital_name,
       gender_code,
       gender,
       age_group,
       sum(if(dt >= date_add('$date', -6), consultation_amount, 0)) consultation_amount_7d,
       sum(if(dt >= date_add('$date', -6), consultation_count, 0))  consultation_count_7d,
       sum(consultation_amount)                                          consultation_amount_30d,
       sum(consultation_count)                                           consultation_count_30d
from dws_trade_hospital_gender_age_group_consultation_1d
where dt >= date_add('$date', -29)
group by hospital_id,
         hospital_name,
         gender_code,
         gender,
         age_group;"
        execute_sql "$sql"
        ;;
    "dws_trade_hospital_gender_age_group_consultation_pay_suc_nd")
        sql="insert overwrite table dws_trade_hospital_gender_age_group_consultation_pay_suc_nd
    partition (dt = '$date')
select hospital_id,
       hospital_name,
       gender_code,
       gender,
       age_group,
       sum(if(dt >= date_add('$date', -6), consultation_pay_suc_amount, 0)) consultation_pay_suc_amount_7d,
       sum(if(dt >= date_add('$date', -6), consultation_pay_suc_count, 0))  consultation_pay_suc_count_7d,
       sum(consultation_pay_suc_amount)                                          consultation_pay_suc_amount_30d,
       sum(consultation_pay_suc_count)                                           consultation_pay_suc_count_30d
from dws_trade_hospital_gender_age_group_consultation_pay_suc_1d
where dt >= date_add('$date', -29)
group by hospital_id,
         hospital_name,
         gender_code,
         gender,
         age_group;"
        execute_sql "$sql"
        ;;
    "dws_trade_hospital_gender_age_group_prescription_nd")
        sql="insert overwrite table dws_trade_hospital_gender_age_group_prescription_nd
    partition (dt = '$date')
select hospital_id,
       hospital_name,
       gender_code,
       gender,
       age_group,
       sum(if(dt >= date_add('$date', -6), prescription_amount, 0)) prescription_amount_7d,
       sum(if(dt >= date_add('$date', -6), prescription_count, 0))  prescription_count_7d,
       sum(prescription_amount)                                          prescription_amount_30d,
       sum(prescription_count)                                           prescription_count_30d
from dws_trade_hospital_gender_age_group_prescription_1d
where dt >= date_add('$date', -29)
group by hospital_id,
         hospital_name,
         gender_code,
         gender,
         age_group;"
        execute_sql "$sql"
        ;;
    "dws_trade_hospital_gender_age_group_prescription_pay_suc_nd")
        sql="insert overwrite table dws_trade_hospital_gender_age_group_prescription_pay_suc_nd
    partition (dt = '$date')
select hospital_id,
       hospital_name,
       gender_code,
       gender,
       age_group,
       sum(if(dt >= date_add('$date', -6), prescription_pay_suc_amount, 0)) prescription_pay_suc_amount_7d,
       sum(if(dt >= date_add('$date', -6), prescription_pay_suc_count, 0))  prescription_pay_suc_count_7d,
       sum(prescription_pay_suc_amount)                                          prescription_pay_suc_amount_30d,
       sum(prescription_pay_suc_count)                                           prescription_pay_suc_count_30d
from dws_trade_hospital_gender_age_group_prescription_pay_suc_1d
where dt >= date_add('$date', -29)
group by hospital_id,
         hospital_name,
         gender_code,
         gender,
         age_group;"
        execute_sql "$sql"
        ;;
    "dws_trade_doctor_consultation_nd")
        sql="insert overwrite table dws_trade_doctor_consultation_nd
    partition (dt = '$date')
select doctor_id,
       doctor_name,
       sum(if(dt >= date_add('$date', -6), consultation_count, 0)) consultation_count_7d,
       sum(consultation_count)                                          consultation_count_30d
from dws_trade_doctor_consultation_1d
where dt >= date_add('$date', -29)
group by doctor_id,
         doctor_name;"
        execute_sql "$sql"
        ;;
    *)
        echo "不支持的表名: $table_name"
        exit 1
        ;;
esac

echo "数据加载完成"

